package game.adm.dao.impl;

import game.adm.dao.ChargeDAO;
import game.adm.dao.dto.ChargeDTO;
import game.adm.struts.form.ChargeForm;
import game.web.base.Constants;
import game.web.base.DateUtils;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import projectx.core.base.ProjectXSystemException;
import projectx.core.dao.AbstractDAO;
import projectx.core.dao.DataAccessException;
import projectx.core.dao.ExceptionHandler;
import projectx.core.dao.impl.PreparedStatementBuilder;

public class ChargeDAOImpl extends AbstractDAO implements ChargeDAO {

	public enum field {
		id, userid, money,orderstatus,orderno,cardno,createtime,updatetime,createip,updateip,username;
	}

	public ChargeDAOImpl() throws ProjectXSystemException {
		super();
	}
	
	private List<ChargeDTO> toList(ResultSet rs) throws Exception{
		List<ChargeDTO> list = new ArrayList<ChargeDTO>();
		while (rs.next()) {
			String id = getString(rs.getString(field.id.ordinal()+1));
			String uid = getString(rs.getString(field.userid.ordinal()+1));
			double money = rs.getDouble(field.money.ordinal()+1);
			int orderstatus = rs.getInt(field.orderstatus.ordinal()+1);
			String orderno = getString(rs.getString(field.orderno.ordinal()+1));
			String cardno = getString(rs.getString(field.cardno.ordinal()+1));
			String createtime = rs.getString(field.createtime.ordinal()+1);
			String updatetime = rs.getString(field.updatetime.ordinal()+1);
			String createip = getString(rs.getString(field.createip.ordinal()+1));
			String updateip = getString(rs.getString(field.updateip.ordinal()+1));
			String username = getString(rs.getString(field.username.ordinal()+1));
			ChargeDTO dto = new ChargeDTO();
			dto.setId(id);
			dto.setUserid(uid);
			dto.setMoney(money);
			dto.setOrderstatus(orderstatus);
			dto.setOrderno(orderno);
			dto.setCardno(cardno);
			dto.setCreatetime(createtime);
			dto.setUpdatetime(updatetime);
			dto.setCreateip(createip);
			dto.setUpdateip(updateip);
			dto.setUsername(username);
			list.add(dto);
		}
		return list;
	}

	@Override
	public List<ChargeDTO> list(ChargeForm chargeForm)
			throws DataAccessException {
		// TODO Auto-generated method stub
		ResultSet rs = null;
		PreparedStatement ps = null;
		List<ChargeDTO> list = null;
		StringBuilder sb = new StringBuilder();
		String id = chargeForm.getId();
		String userName = chargeForm.getUserName();
		String orderStatus = chargeForm.getOrderStatus();
		String startTime = chargeForm.getStartTime();
		String endTime = chargeForm.getEndTime();
		String queryType = chargeForm.getQueryType();
		int currentPage = 1;
		int pageSize = Constants.ADM_RECORD_PER_PAGE;
		List<String> parameterValues = new ArrayList<String>();
		List<Integer> parameterTypes = new ArrayList<Integer>();
		try{
			currentPage = Integer.parseInt(chargeForm.getCurrentPage());
		}catch(Exception e){
			logError(e);
		}
		String format = "yyyy-MM-dd";
		if("2".equals(queryType)){        //今日充值查询
			startTime = DateUtils.getCurrentDate(format);
			endTime = DateUtils.getCurrentDate(format);
		}else if("3".equals(queryType)){   //本周充值查询
			startTime = DateUtils.getMondayDate(format);
			endTime = DateUtils.getSundayDate(format);
		}else if("4".equals(queryType)){    //本月充值查询
			startTime = DateUtils.getFirstDateOfMonth(format);
			endTime = DateUtils.getLastDateOfMonth(format);
		}
		if(null != id && id.length() > 0){
			sb.append(" and a.id like ? ");
			parameterValues.add("%" + id + "%");
			parameterTypes.add(Types.VARCHAR);
		}
		if(null != userName && userName.length() > 0){
			sb.append(" and b.username like ? ");
			parameterValues.add("%" + userName + "%");
			parameterTypes.add(Types.VARCHAR);
		}
		if(null != orderStatus && orderStatus.length() > 0){
			sb.append(" and a.orderstatus =? ");
			parameterValues.add(orderStatus);
			parameterTypes.add(Types.VARCHAR);
		}
		if(null != startTime && startTime.length() > 0){
			sb.append(" and a.createtime >=? ");
			parameterValues.add(startTime + " 00:00:00");
			parameterTypes.add(Types.VARCHAR);
		}
		if(null != endTime && endTime.length() > 0){
			sb.append(" and a.createtime <=? ");
			parameterValues.add(endTime + " 23:59:59");
			parameterTypes.add(Types.VARCHAR);
		}
		
		try {
			//总记录查询
			_builder = (PreparedStatementBuilder) SqlLoader.getBuilders().get("ADM_ChargeDAO_SELECT_TOTAL_COUNT");
			String totalSQL = _builder.getSql(null, null) + sb.toString();
			ps = _builder.createStatement(_conn, totalSQL, false, false);
			fillParameters(_builder,ps,parameterValues,parameterTypes);
			debugSql();
			rs = ps.executeQuery();
			int totalRecord = 0;
			if(rs.next()){
				totalRecord = rs.getInt(1);
			}
			if(null != rs){
				rs.close();
			}
			if(null != ps){
				ps.close();
			}
			if(totalRecord <= 0){
				return list;
			}
			
			//成功充值记录、总金额查询
			_builder = (PreparedStatementBuilder) SqlLoader.getBuilders().get("ADM_ChargeDAO_SELECT_SUCCESS_COUNT");
			String successSQL = _builder.getSql(null, null) + sb.toString();
			ps = _builder.createStatement(_conn, successSQL, false, false);
			fillParameters(_builder,ps,parameterValues,parameterTypes);
			debugSql();
			rs = ps.executeQuery();
			int successRecord = 0;
			double successAmount = 0;
			if(rs.next()){
				successRecord = rs.getInt(1);
				successAmount = rs.getDouble(2);
			}
			if(null != rs){
				rs.close();
			}
			if(null != ps){
				ps.close();
			}
			
			//失败记录、总金额查询
			_builder = (PreparedStatementBuilder) SqlLoader.getBuilders().get("ADM_ChargeDAO_SELECT_FAIL_COUNT");
			String failSQL = _builder.getSql(null, null) + sb.toString();
			ps = _builder.createStatement(_conn, failSQL, false, false);
			fillParameters(_builder,ps,parameterValues,parameterTypes);
			debugSql();
			rs = ps.executeQuery();
			int failRecord = 0;
			double failAmount = 0;
			if(rs.next()){
				failRecord = rs.getInt(1);
				failAmount = rs.getDouble(2);
			}
			if(null != rs){
				rs.close();
			}
			if(null != ps){
				ps.close();
			}
			
			//处理中记录、总金额查询
			_builder = (PreparedStatementBuilder) SqlLoader.getBuilders().get("ADM_ChargeDAO_SELECT_PROCESS_COUNT");
			String processSQL = _builder.getSql(null, null) + sb.toString();
			ps = _builder.createStatement(_conn, processSQL, false, false);
			fillParameters(_builder,ps,parameterValues,parameterTypes);
			debugSql();
			rs = ps.executeQuery();
			int processRecord = 0;
			double processAmount = 0;
			if(rs.next()){
				processRecord = rs.getInt(1);
				processAmount = rs.getDouble(2);
			}
			if(null != rs){
				rs.close();
			}
			if(null != ps){
				ps.close();
			}
			
			//分页查询记录
			sb.append(" order by a.createtime desc ");
			_builder = (PreparedStatementBuilder) SqlLoader.getBuilders().get("ADM_ChargeDAO_SELECT");
			int totalPage = 0;
			if(totalRecord % pageSize == 0){
				totalPage = totalRecord / pageSize;
			}else{
				totalPage = totalRecord / pageSize + 1;
			}
			if(currentPage <= 0){
				currentPage = 1;
			}else if (currentPage > totalPage){
				currentPage = totalPage;
			}
			String hasPrevious = "false";
			String hasNext = "false";
			if(currentPage > 1){
				hasPrevious = "true";
			}
			if(currentPage < totalPage){
				hasNext = "true";
			}
			String limitSQL = _builder.getSql(null, null) + sb.toString() + " limit " + ((currentPage-1)*pageSize) + "," + pageSize;
			ps = _builder.createStatement(_conn, limitSQL, false, false);
			fillParameters(_builder,ps,parameterValues,parameterTypes);
			debugSql();
			rs = ps.executeQuery();
			list = toList(rs);
			
			chargeForm.setCurrentPage(new Integer(currentPage).toString());
			chargeForm.setFailAmount(new Double(failAmount).toString());
			chargeForm.setFailRecord(new Integer(failRecord).toString());
			chargeForm.setFirstPage("1");
			chargeForm.setHasPrevious(hasPrevious);
			chargeForm.setHasNext(hasNext);
			chargeForm.setNextPage(new Integer(currentPage + 1).toString());
			chargeForm.setPreviousPage(new Integer(currentPage - 1).toString());
			chargeForm.setProcessAmount(new Double(processAmount).toString());
			chargeForm.setProcessRecord(new Integer(processRecord).toString());
			chargeForm.setSuccessAmount(new Double(successAmount).toString());
			chargeForm.setSuccessRecord(new Integer(successRecord).toString());
			chargeForm.setTotalPage(new Integer(totalPage).toString());
			chargeForm.setTotalRecord(new Integer(totalRecord).toString());
		} catch (Exception e) {
			errorSql(e);
			_exHandler.handleQueryException(e, ExceptionHandler.UNKNOWN);
		} finally {
			try {
				if (null != rs) {
					rs.close();
				}
				if (null != ps) {
					ps.close();
				}
				if(null != _conn){
					_conn.close();
				}
			} catch (SQLException e) {
				logError(e);
			}
		}
		return list;
	}
	
	private void fillParameters(PreparedStatementBuilder builder,PreparedStatement ps,List<String> parameterValues,List<Integer> parameterTypes) throws Exception{
		if(null != parameterValues && parameterValues.size() >0){
			for(int i = 0 ;i < parameterValues.size() ; i ++){
				int type = Integer.parseInt(parameterTypes.get(i).toString());
				builder.setParameter(ps, (i + 1), parameterValues.get(i) , type);
			}
		}
	}

	@Override
	public boolean update(String id, String orderStatus,String updateip)
			throws DataAccessException {
		// TODO Auto-generated method stub
		int result = 0;
		PreparedStatement ps = null;
		String sql = null;
		try {
			_builder = (PreparedStatementBuilder) SqlLoader.getBuilders().get(
					"ADM_CHargeDAO_UPDATE_CHARGE");
			sql = _builder.getSql(null, null);
			ps = _builder.createStatement(_conn, sql, false, false);
			_builder.setParameter(ps, 1, orderStatus, Types.VARCHAR);
			_builder.setParameter(ps, 2, DateUtils.getCurrentDate("yyyy-MM-dd HH:mm:ss"), Types.VARCHAR);
			_builder.setParameter(ps, 3, updateip,Types.VARCHAR);
			_builder.setParameter(ps, 4, id,Types.VARCHAR);
			
			debugSql();
			result = ps.executeUpdate();

		} catch (Exception e) {
			errorSql(e);
			_exHandler.handleUpdateException(e, ExceptionHandler.UNKNOWN);
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
			} catch (SQLException e) {
				logError(e);
			}
		}
		if (result == 0) {
			_exHandler.handleUpdateException(null, ExceptionHandler.NOTUPDATED);
		} else if (result > 1) {
			_exHandler.handleUpdateException(null,
					ExceptionHandler.MULTIUPDATED);
		}
		return true;
	}

	@Override
	public ChargeDTO getChargeByID(String id) throws DataAccessException {
		// TODO Auto-generated method stub
		ChargeDTO chargeDTO = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			_builder = (PreparedStatementBuilder) SqlLoader.getBuilders().get("ADM_ChargeDAO_GET_CHARGE_RECORD");
			String sql = _builder.getSql(null, null);
			ps = _builder.createStatement(_conn, sql, false, false);
			_builder.setParameter(ps, field.id.ordinal()+1, id , Types.VARCHAR);
			debugSql();
			rs = ps.executeQuery();
			if(rs.next()){
				String orderid = getString(rs.getString(field.id.ordinal()+1));
				String uid = getString(rs.getString(field.userid.ordinal()+1));
				double money = rs.getDouble(field.money.ordinal()+1);
				int orderstatus = rs.getInt(field.orderstatus.ordinal()+1);
				String orderno = getString(rs.getString(field.orderno.ordinal()+1));
				String cardno = getString(rs.getString(field.cardno.ordinal()+1));
				String createtime = rs.getString(field.createtime.ordinal()+1);
				String updatetime = rs.getString(field.updatetime.ordinal()+1);
				String createip = getString(rs.getString(field.createip.ordinal()+1));
				String updateip = getString(rs.getString(field.updateip.ordinal()+1));
				String username = getString(rs.getString(field.username.ordinal()+1));
				chargeDTO = new ChargeDTO();
				chargeDTO.setId(orderid);
				chargeDTO.setUserid(uid);
				chargeDTO.setMoney(money);
				chargeDTO.setOrderstatus(orderstatus);
				chargeDTO.setOrderno(orderno);
				chargeDTO.setCardno(cardno);
				chargeDTO.setCreatetime(createtime);
				chargeDTO.setUpdatetime(updatetime);
				chargeDTO.setCreateip(createip);
				chargeDTO.setUpdateip(updateip);
				chargeDTO.setUsername(username);
			}
		} catch (Exception e) {
			errorSql(e);
			_exHandler.handleQueryException(e, ExceptionHandler.UNKNOWN);
		} finally {
			try {
				if (null != rs) {
					rs.close();
				}
				if (null != ps) {
					ps.close();
				}
				if(null != _conn){
					_conn.close();
				}
			} catch (SQLException e) {
				logError(e);
			}
		}
		return chargeDTO;
	}
}
